package com.gis.mapper;

import com.gis.dto.BiologyReportDTO;
import com.gis.entity.BiologyData;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author bobo
 * @since 2024-11-28
 */
public interface BiologyDataMapper extends BaseMapper<BiologyData> {
    @Select("SELECT " +
            "bl.station_id, " +
            "s.name, " +
            "s.latitude, " +
            "s.longitude, " +
            "COALESCE(AVG(bl.mercury), -1.0) AS mercury, " +
            "COALESCE(AVG(bl.cadmium), -1.0) AS cadmium, " +
            "COALESCE(AVG(bl.lead), -1.0) AS lead, " +
            "COALESCE(AVG(bl.chromium), -1.0) AS chromium, " +
            "COALESCE(AVG(bl.arsenic), -1.0) AS arsenic, " +
            "COALESCE(AVG(bl.copper), -1.0) AS copper, " +
            "COALESCE(AVG(bl.zinc), -1.0) AS zinc, " +
            "COALESCE(AVG(bl.tph), -1.0) AS tph " +
            "FROM biology_data bl " +
            "JOIN station_data s ON bl.station_id = s.id " +
            "WHERE bl.year = #{year} AND bl.season = #{season} " +
            "GROUP BY bl.station_id, s.name, s.latitude, s.longitude " +
            "ORDER BY s.name")
    List<BiologyReportDTO> getBiologyQualityAvgByYearAndSeason(int year, String season);

    @Select("SELECT * " +
            "FROM biology_data " +
            "WHERE station_id = #{id} AND #{param} IS NOT NULL " +
            "ORDER BY year DESC, " +
            "         CASE " +
            "             WHEN season = 'spring' THEN 1 " +
            "             WHEN season = 'summer' THEN 2 " +
            "             WHEN season = 'fall' THEN 3 " +
            "             WHEN season = 'winter' THEN 4 " +
            "             ELSE 5 " +
            "         END DESC " +
            "LIMIT #{n}")
    List<BiologyData> getLastN(String param, int n, int id);

}
